

create schema ULTIMO_COMMIT
GO
 USE [GD1C2012]  
GO  

CREATE TABLE ULTIMO_COMMIT.Funcionalidad (
	ID_Funcionalidad varchar(255) primary key, -- Puede pasar a ser un int IDENTITY
	Nombre varchar(255) NOT NULL
)

CREATE TABLE ULTIMO_COMMIT.Rol_Funcionalidad(
	ID_Rol int,
	ID_Funcionalidad varchar(255) -- Puede pasar a ser un int
)

CREATE TABLE ULTIMO_COMMIT.Rol(
	ID_Rol int NOT NULL primary key, -- debe ser IDENTITY?
	Nombre varchar(255) NOT NULL unique , -- se toma la desicion que el nombre sea unico por rol
	Habilitado bit default 1
)

create table ULTIMO_COMMIT.Usuario_Rol(
	ID_Usuario int,
	ID_Rol int 
)



create table ULTIMO_COMMIT.Usuario(
	ID_Usuario int IDENTITY primary key,
	Usuario varchar(255) unique,
	Password varchar (255),
	Habilitado bit default 1
)




---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------



create table ULTIMO_COMMIT.Cliente(
	ID_Cliente int IDENTITY primary key, 
	Dni numeric(18, 0) unique , -- se toma la desicion de que el dni sea unico
	Nombre varchar(255) not null ,
	Apellido varchar(255)  not null,
	Telefono numeric(18, 0) unique not null,
	Direccion varchar(255)  not null,
	Mail varchar(255)  null,
	Fecha_Nacimiento datetime not null,
	Habilitado bit default 1 ,
)


create table ULTIMO_COMMIT.Turno(
	ID_Turno int IDENTITY primary key,
	Turno_Descripcion varchar(255) unique, -- se toma la desicion que la descripcion debe sea unico
	Turno_Hora_Inicio numeric(18,0) not null,
	Turno_Hora_Fin numeric(18,0) not null,
	Turno_Valor_Ficha numeric(18,2) not null,
	Turno_Valor_Bandera numeric(18,2) not null,
	Habilitado bit default 1
)


create table ULTIMO_COMMIT.Chofer(
	ID_Chofer int IDENTITY primary key,
	Chofer_Dni numeric(18,0) unique,
	Chofer_Nombre varchar(255) not null,
	Chofer_Apellido varchar(255) not null,
	Chofer_Direccion varchar(255) not null,
	Chofer_Telefono numeric(18,0) not null,
	Chofer_Mail varchar(50) null ,
	Chofer_Fecha_Nac datetime not null,
	Habilitado bit default 1
)

CREATE TABLE ULTIMO_COMMIT.Facturacion(
	ID_Facturacion int IDENTITY primary key,
	ID_Cliente int NOT NULL,
	Fecha_Inicio datetime NOT NULL,
	Fecha_Fin datetime NOT NULL,
	Importe int NOT NULL,
)

------------------------------------------------------------------------
--------------          Reloj                      ---------------------
------------------------------------------------------------------------

CREATE TABLE ULTIMO_COMMIT.Reloj_Marca(
	ID_Marca int IDENTITY primary key NOT NULL,
	Marca varchar(255) unique
)

CREATE TABLE ULTIMO_COMMIT.Reloj_Modelo(
	ID_Modelo int IDENTITY primary key NOT NULL,
	Modelo varchar(255) 
)

---- es necesaria?????????-------------------------------

CREATE TABLE ULTIMO_COMMIT.Reloj_Marca_Modelo(
	ID_Marca int NOT NULL,
	ID_Modelo int NOT NULL
)

----------------------------------------------------------

CREATE TABLE ULTIMO_COMMIT.Reloj(
	ID_Reloj int IDENTITY primary key NOT NULL,
	ID_Marca int NOT NULL,
	Numero_Serie varchar(18)  NOT NULL, -- se quito el unique por el problema de la migracion que las tablas no tiene Num_Serie
	Fecha_Verificacion datetime NOT NULL,
	ID_Modelo int NOT NULL,
	Habilitado bit default 1
)
----------------------------------------------------------------------------

------------------------------------------------------------------------
--------------            Auto                     ---------------------
------------------------------------------------------------------------


CREATE TABLE ULTIMO_COMMIT.Auto_Marca(
	ID_Marca int IDENTITY primary key NOT NULL,
	Marca varchar(255) unique
)

CREATE TABLE ULTIMO_COMMIT.Auto_Modelo(
	ID_Modelo int IDENTITY primary key NOT NULL,
	Modelo varchar(255) 
)

---- es necesaria?????????-------------------------------

CREATE TABLE ULTIMO_COMMIT.Auto_Marca_Modelo(
	ID_Marca int NOT NULL,
	ID_Modelo int NOT NULL
)

----------------------------------------------------------

CREATE TABLE ULTIMO_COMMIT.Auto (
	ID_Auto int IDENTITY primary key NOT NULL,
	Patente varchar(10) unique NOT NULL,
	Licencia varchar(26)  unique NOT NULL,
	Rodado varchar(10)  NOT NULL,
	ID_Marca int NOT NULL,
	ID_Modelo int NOT NULL,
	ID_Reloj int  NOT NULL,
	Habilitado bit default 1
)

----------------------------------------------------------------------------


CREATE TABLE ULTIMO_COMMIT.Asignacion_Chofer_Auto(
	Fecha datetime NOT NULL,
	ID_Auto int NOT NULL,
	ID_Chofer int NOT NULL,
	ID_Turno int NOT NULL

)

CREATE TABLE ULTIMO_COMMIT.Rendicion(
	ID_Rendicion int IDENTITY primary key NOT NULL,
	Fecha datetime NOT NULL,
	ID_Chofer int NOT NULL,
	ID_Turno int NOT NULL,
	ImporteTotal int NOT NULL,
)

CREATE TABLE ULTIMO_COMMIT.Tipo_Viaje(
	ID_Tipo_Viaje int IDENTITY primary key NOT NULL,
	Nombre varchar(255)
)

CREATE TABLE ULTIMO_COMMIT.Viajes(
	ID_Viaje int IDENTITY primary key NOT NULL,
	ID_Tipo_Viaje int NOT NULL,
	ID_Chofer int NOT NULL,
	ID_Turno int NOT NULL,
	Cantidad_Fichas numeric(18,0) NOT NULL,
	Fecha datetime NOT NULL,
	ID_Cliente int NULL,
	Importe int NOT NULL,
)




----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-----------   creacion de FK para todas las tablas -------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

---------------    LOGIN ------------------------

alter table ULTIMO_COMMIT.Rol_Funcionalidad add foreign key (ID_Funcionalidad) references ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad)

alter table ULTIMO_COMMIT.Rol_Funcionalidad add foreign key (ID_Rol) references ULTIMO_COMMIT.Rol(ID_Rol)

alter table ULTIMO_COMMIT.Usuario_Rol add foreign key (ID_Rol) references ULTIMO_COMMIT.Rol(ID_Rol)

alter table ULTIMO_COMMIT.Usuario_Rol add foreign key (ID_Usuario) references ULTIMO_COMMIT.Usuario(ID_Usuario)


---------------    VIAJES ------------------------

-- que diferencia hay con la de abajo
--alter table ULTIMO_COMMIT.Tipo_Viaje add foreign key (ID_Tipo_Viaje) references ULTIMO_COMMIT.Viajes (ID_Tipo_Viaje)

alter table ULTIMO_COMMIT.Viajes add foreign key (ID_Tipo_Viaje) references ULTIMO_COMMIT.Tipo_Viaje (ID_Tipo_Viaje)

alter table ULTIMO_COMMIT.Viajes add foreign key (ID_Chofer) references ULTIMO_COMMIT.Chofer (ID_Chofer)

alter table ULTIMO_COMMIT.Viajes add foreign key (ID_Turno) references ULTIMO_COMMIT.Turno (ID_Turno)

alter table ULTIMO_COMMIT.Viajes add foreign key (ID_Cliente) references ULTIMO_COMMIT.Cliente (ID_Cliente)

---------------    CLIENTE ------------------------

alter table ULTIMO_COMMIT.Facturacion add foreign key (ID_Cliente) references ULTIMO_COMMIT.Cliente (ID_Cliente)

---------------    RELOJ ------------------------

alter table ULTIMO_COMMIT.Reloj add foreign key (ID_Marca) references ULTIMO_COMMIT.Reloj_Marca (ID_Marca)

alter table ULTIMO_COMMIT.Reloj add foreign key (ID_Modelo) references ULTIMO_COMMIT.Reloj_Modelo (ID_Modelo)

alter table ULTIMO_COMMIT.Reloj_Marca_Modelo add foreign key (ID_Marca) references ULTIMO_COMMIT.Reloj_Marca (ID_Marca)

alter table ULTIMO_COMMIT.Reloj_Marca_Modelo add foreign key (ID_Modelo) references ULTIMO_COMMIT.Reloj_Modelo (ID_Modelo)

---------------    AUTO ------------------------

alter table ULTIMO_COMMIT.Auto add foreign key (ID_Marca) references ULTIMO_COMMIT.Auto_Marca (ID_Marca)

alter table ULTIMO_COMMIT.Auto add foreign key (ID_Modelo) references ULTIMO_COMMIT.Auto_Modelo (ID_Modelo)

alter table ULTIMO_COMMIT.Auto_Marca_Modelo add foreign key (ID_Marca) references ULTIMO_COMMIT.Auto_Marca (ID_Marca)

alter table ULTIMO_COMMIT.Auto_Marca_Modelo add foreign key (ID_Modelo) references ULTIMO_COMMIT.Auto_Modelo (ID_Modelo)

---------------    AUTO  - RELOJ ----------------

alter table ULTIMO_COMMIT.Auto add foreign key (ID_Reloj) references ULTIMO_COMMIT.Reloj (ID_Reloj)

---------------    ASIGNAION --------------------

alter table ULTIMO_COMMIT.Asignacion_Chofer_Auto add foreign key (ID_Auto) references ULTIMO_COMMIT.Auto (ID_Auto)

alter table ULTIMO_COMMIT.Asignacion_Chofer_Auto add foreign key (ID_Chofer) references ULTIMO_COMMIT.Chofer (ID_Chofer)

alter table ULTIMO_COMMIT.Asignacion_Chofer_Auto add foreign key (ID_Turno) references ULTIMO_COMMIT.Turno (ID_Turno)

---------------    RENDICION --------------------

alter table ULTIMO_COMMIT.Rendicion add foreign key (ID_Turno) references ULTIMO_COMMIT.Turno (ID_Turno)

alter table ULTIMO_COMMIT.Rendicion add foreign key (ID_Chofer) references ULTIMO_COMMIT.Chofer (ID_Chofer)




----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-----------             Store Procedure/ Funcions  -------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------





------ creo sp_verificacion_Usuario ------

create  procedure ULTIMO_COMMIT.sp_verificacion_Usuario(@usuario varchar(255),@password varchar(255) )
AS
BEGIN transaction

if(select count(*) from ULTIMO_COMMIT.Usuario where Usuario=@usuario)!=1
	begin
		raisError('No existe el usuario',16,1)
	end
else if (select count(*) from ULTIMO_COMMIT.Usuario where Usuario=@usuario and Password=@password)!= 1
	begin
		raisError('Error Password',16,1)
	end
else if(select count(*) from ULTIMO_COMMIT.Usuario where Usuario=@usuario and Habilitado = 1 )!=1
	begin
		raisError('Usuario No Habilitado, Comuniquese con el administrador',16,1)
	end
else if (select count(*)
	from ULTIMO_COMMIT.Usuario U inner join ULTIMO_COMMIT.Usuario_Rol UR on U.ID_Usuario=UR.ID_Usuario 
	where U.Usuario=@usuario)<1
	begin
		raisError('El usuario NO tiene Rol asignado',16,1)
	end
else
	begin
		 select distinct F.Nombre 
                        from ULTIMO_COMMIT.Usuario U inner join ULTIMO_COMMIT.Usuario_Rol UR on U.ID_Usuario = UR.ID_Usuario 
                        inner join ULTIMO_COMMIT.Rol R on UR.ID_Rol = R.ID_Rol 
                        inner join ULTIMO_COMMIT.Rol_Funcionalidad RF on R.ID_Rol = RF.ID_Rol 
                        inner join ULTIMO_COMMIT.Funcionalidad F on RF.ID_Funcionalidad = F.ID_Funcionalidad 
                        where R.Habilitado =1 
                        AND U.Usuario = @usuario
                        AND U.Password = @password
		
	end 
commit


























----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-----------             Migracion datos			   -------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------


---------------    Funcionalidad --------------------

insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmCliente', 'ABM de Cliente')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmRol', 'ABM de Rol')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmUsuario', 'ABM de Usuario')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmAuto', 'ABM de Auto')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmReloj', 'ABM de Reloj')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmChofer', 'ABM de Chofer')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('abmTurno', 'ABM de Turno')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('asignacionChoferViaje', 'Asignacion Chofer-Viaje')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('registroViajes', 'Registro de Viajes')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('rendicionCuentaChofer', 'Rendicion de Cuenta del Chofer')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('facturacionCliente', 'Facturacion a Cliente')
insert into ULTIMO_COMMIT.Funcionalidad(ID_Funcionalidad, Nombre)
values('listadoEstadistico', 'Listado Estadistico')

---------------    rol --------------------

insert into ULTIMO_COMMIT.Rol (ID_Rol,Nombre,Habilitado)
values (1, 'Administrador General', 1)

insert into ULTIMO_COMMIT.Rol_Funcionalidad (ID_Rol, ID_Funcionalidad)
select 1 , ID_Funcionalidad from ULTIMO_COMMIT.Funcionalidad

-- usuario admin -- password w23e

insert into ULTIMO_COMMIT.Usuario (Usuario,Password)
values ('8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918','e6b87050bfcb8143fcb8db0170a4dc9ed00d904ddd3e2a4ad1b1e8dc0fdc9be7')

insert into ULTIMO_COMMIT.Usuario_Rol(ID_Usuario,ID_Rol)
select ID_Usuario, 1  from  ULTIMO_COMMIT.Usuario where Usuario='8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918'




---------------    Turno --------------------

INSERT INTO ULTIMO_COMMIT.Turno 
SELECT  Turno_Descripcion, Turno_Hora_Inicio, Turno_Hora_Fin, Turno_Valor_Ficha ,Turno_Valor_Bandera, 1
FROM gd_esquema.Maestra
Group by Turno_Descripcion, Turno_Hora_Inicio, Turno_Hora_Fin, Turno_Valor_Ficha ,Turno_Valor_Bandera

---------------    Chofer --------------------

INSERT INTO ULTIMO_COMMIT.Chofer 
SELECT Chofer_Dni,Chofer_Nombre,Chofer_Apellido,Chofer_Direccion,Chofer_Telefono,Chofer_Mail,Chofer_Fecha_Nac, 1
FROM gd_esquema.Maestra
Group by Chofer_Dni,Chofer_Nombre,Chofer_Apellido,Chofer_Direccion,Chofer_Telefono,Chofer_Mail,Chofer_Fecha_Nac


---------------    Cliente --------------------
INSERT INTO ULTIMO_COMMIT.Cliente 
SELECT Cliente_Dni ,Cliente_Nombre,Cliente_Apellido,Cliente_Telefono,Cliente_Direccion,Cliente_Mail,Cliente_Fecha_Nac, 1
FROM gd_esquema.Maestra
where Cliente_Dni >=0
Group by Cliente_Dni,Cliente_Nombre,Cliente_Apellido,Cliente_Telefono,Cliente_Direccion,Cliente_Mail,Cliente_Fecha_Nac

---------------    Reloj --------------------

INSERT INTO ULTIMO_COMMIT.Reloj_Marca 
SELECT Reloj_Marca
FROM gd_esquema.Maestra
Group by Reloj_Marca

INSERT INTO ULTIMO_COMMIT.Reloj_Modelo
SELECT Reloj_Modelo
FROM gd_esquema.Maestra
Group by Reloj_Modelo

-- al los relojes existentes se le pone el numero de serie 0 ya que en la tabla original no se encuentra la informacion.

--alter table ULTIMO_COMMIT.Reloj drop constraint UQ__Reloj__540C7B00

INSERT INTO ULTIMO_COMMIT.Reloj
SELECT Ma.ID_Marca, '0' as Numero_Serie, M.Reloj_Fecha_Ver, Mo.ID_Modelo, '1' as Habilitado
FROM gd_esquema.Maestra M inner join ULTIMO_COMMIT.Reloj_Modelo Mo on M.Reloj_Modelo=Mo.Modelo
						  inner join ULTIMO_COMMIT.Reloj_Marca Ma on M.Reloj_Marca=Ma.Marca
Group by Ma.ID_Marca, M.Reloj_Fecha_Ver, Mo.ID_Modelo

---------------    Autos --------------------
INSERT INTO ULTIMO_COMMIT.Auto_Marca 
SELECT Auto_Marca
FROM gd_esquema.Maestra
Group by Auto_Marca

INSERT INTO ULTIMO_COMMIT.Auto_Modelo
SELECT Auto_Modelo
FROM gd_esquema.Maestra
Group by Auto_Modelo

--INSERT INTO ULTIMO_COMMIT.Auto_Marca_Modelo

INSERT INTO ULTIMO_COMMIT.Auto
SELECT Ma.ID_Marca, '0' as Numero_Serie, M.Reloj_Fecha_Ver, Mo.ID_Modelo, '1' as Habilitado
FROM gd_esquema.Maestra M inner join ULTIMO_COMMIT.Reloj_Modelo Mo on M.Reloj_Modelo=Mo.Modelo
						  inner join ULTIMO_COMMIT.Reloj_Marca Ma on M.Reloj_Marca=Ma.Marca
Group by Ma.ID_Marca, M.Reloj_Fecha_Ver, Mo.ID_Modelo


---------------    Viajes --------------------
INSERT INTO ULTIMO_COMMIT.Tipo_Viaje( Nombre)
values('Calle')
INSERT INTO ULTIMO_COMMIT.Tipo_Viaje( Nombre)
values('Cliente')
